package com.prdiot.poetry.dao;

import com.prdiot.poetry.domains.Poems;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;
import java.util.Optional;

public interface PoemsRepository extends JpaRepository<Poems, Integer> {

    //百度推送 随机选取50条
    @Query(value = "SELECT * FROM poems ORDER BY rand() LIMIT 400", nativeQuery=true)
    List<Poems> getRandPoetryToBaidu();

    //首页随机推荐诗文
    @Query(value = "SELECT * FROM poems WHERE id>=(SELECT FLOOR(RAND() * (SELECT MAX(id) FROM poems))) AND LENGTH(translation)>0 AND LENGTH(explanation)>0 AND LENGTH(appreciation)>0 ORDER BY id LIMIT 10", nativeQuery=true)
    List<Poems> getRandPoetry();

    //查询诗文
    @Query("select p from Poems p where p.title like %?1% or p.body like %?1% or p.author like %?1%")
    Page<Poems> searchByKey(@Param("key") String key, Pageable pageable);

    //根据作者ID 随机推荐诗文
    @Query(value = "SELECT * FROM poems WHERE  author_id=:authorId ORDER BY rand() LIMIT 10", nativeQuery=true)
    List<Poems> getRandPoetryByAuthorId(String authorId);

    //获取诗文朝代列表
    @Query(value = "SELECT distinct dynasty,dynasty_id FROM poems order by dynasty_id asc", nativeQuery=true)
    List<Map<String, String>> listDynasty();

    //按照tag查询诗文
    @Query("select p from Poems p where p.tag like %?1% ")
    Page<Poems> searchPoemsByTag(@Param("key") String key, Pageable pageable);

    //按照authorId查询诗文
    @Query("select p from Poems p where p.authorId=:authorId")
    Page<Poems> searchPoemsByAuthorId(@Param("authorId") String authorId, Pageable pageable);

    //按照dynastyId查询诗文
    @Query("select p from Poems p where p.dynastyId=:dynastyId")
    Page<Poems> searchPoemsByDynastyId(@Param("dynastyId") String dynastyId, Pageable pageable);

    //按照type查询诗文
    @Query("select p from Poems p where p.type=:type")
    Page<Poems> searchPoemsByType(@Param("type") String type, Pageable pageable);

    Optional<Poems> findByCode(String code);
}
